from spider.util import MysqlClient

TBL_NAME = 'member_workbench_channel'   # 根据自己的情况，修改表名称
"""
CREATE TABLE `member_workbench_channel` (
  `id` BIGINT(20) NOT NULL,
  `name` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  `channel_count` INT(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
"""

TBL_NAME_subchannel = 'member_workbench_subchannel'
"""

CREATE TABLE `member_workbench_subchannel` (
  `cid` BIGINT(20) NOT NULL,
  `id` BIGINT(20) NOT NULL,
  `name` VARCHAR(256) COLLATE utf8_bin NOT NULL,
  `cover` VARCHAR(1024) COLLATE utf8_bin DEFAULT NULL,
  `subscribed_count` INT(11) NOT NULL,
  `archive_count` INT(11) NOT NULL,
  `featured_count` INT(11) NOT NULL,
  `view_count` BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (`cid`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

"""

TBL_NAME_subchannel_video = 'member_workbench_subchannel_video'
"""
CREATE TABLE `member_workbench_subchannel_video` (
  `cid` INT(11) NOT NULL,
  `scid` INT(11) NOT NULL,
  `id` INT(11) NOT NULL,
  `name` VARCHAR(1024) COLLATE utf8_bin NOT NULL,
  `cover` VARCHAR(1024) COLLATE utf8_bin NOT NULL,
  `view_count` INT(11) NOT NULL,
  `like_count` INT(11) NOT NULL,
  `duration` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  `author_name` VARCHAR(128) COLLATE utf8_bin DEFAULT NULL,
  `author_id` INT(11) DEFAULT NULL,
  `bvid` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  `card_type` VARCHAR(64) COLLATE utf8_bin DEFAULT NULL,
  `sort` VARCHAR(16) COLLATE utf8_bin DEFAULT NULL,
  `filt` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
"""

TBL_NAMEupname = 'new_table'

class SpiderPipeline:
    client = None

    def open_spider(self, spider):
        self.client = MysqlClient()

    def process_item(self, item, spider):
        if spider.name == 'member_workbench_channel':  # 这里一定要判断类型，只有属于自己的Item才能使用
            sql = 'INSERT INTO ' + TBL_NAME + '(id, name, channel_count) VALUES (%s, %s, %s)'
            self.client.execute(sql, [item['id'], item['name'], item['channel_count']])

        if spider.name == 'member_workbench_subchannel':  # 这里一定要判断类型，只有属于自己的Item才能使用
            # print(item)
            sql = 'INSERT INTO ' + TBL_NAME_subchannel + '(cid, id, name, cover, subscribed_count, archive_count, featured_count) VALUES (%s, %s, %s, %s, %s, %s, %s)'
            self.client.execute(sql, [item['cid'], item['id'], item['name'], item['cover'], item['subscribed_count'], item['archive_count'],  item['featured_count']])

        if spider.name == 'member_workbench_subchannel_video':  # 这里一定要判断类型，只有属于自己的Item才能使用
             sql = 'INSERT INTO ' + TBL_NAME_subchannel_video + '(cid, scid, id, name, author_name, author_id, bvid) VALUES (%s, %s, %s, %s, %s, %s, %s)'
             self.client.execute(sql, [item['cid'], item['scid'], item['id'], item['name'], item['author_name'],  item['author_id'],  item['bvid']])

        if spider.name == 'upname':  # 这里一定要判断类型，只有属于自己的Item才能使用
            sql = 'INSERT INTO ' + TBL_NAMEupname + '(mid, name, sex, face, sign, vip, follower, following, view, likes) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
            self.client.execute(sql, [item['mid'], item['name'], item['sex'], item['face'], item['sign'], item['vip'],
                                      item['follower'], item['following'], item['view'], item['likes']])
        return item # 最后一定要返回item